﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTGroup_ZheJiang.IDAL;
using System.Configuration;
using System.Data;
using DTGroup_ZheJiang.Models;
using System.Data.OracleClient;

namespace DTGroup_ZheJiang.OracleDataAccess
{
    public class FlooorStatisticsDao : IFlooorStatistics
    {
        public string connString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;
        /// <summary>
        /// 分页显示楼宇级别统计
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="sqlCondition"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public DataTable GetFlooorStatisticsByPage(string tableName, int PageIndex, int PageSize, string sqlCondition, out int TotalCount)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("P_Day", typeof(string));
            dt.Columns.Add("City", typeof(string));
            dt.Columns.Add("BuildingId", typeof(string));
            dt.Columns.Add("BuildingName", typeof(string));
            dt.Columns.Add("Longitude", typeof(string));
            dt.Columns.Add("Latitude", typeof(string));
            dt.Columns.Add("FloorCount", typeof(string));
            dt.Columns.Add("Floor", typeof(string));
            dt.Columns.Add("GridCount", typeof(string));
            dt.Columns.Add("WeakGridCount", typeof(string));
            dt.Columns.Add("WeakGridPercent", typeof(string));
            dt.Columns.Add("NoGridCount", typeof(string));
            dt.Columns.Add("NoGridPercent", typeof(string));
            dt.Columns.Add("MRCount", typeof(string));
            dt.Columns.Add("WeakMrCount", typeof(string));
            dt.Columns.Add("AvgMRRsrp", typeof(string));
            dt.Columns.Add("MRCoverPercent", typeof(string));
            dt.Columns.Add("S_CELL1", typeof(string));
            dt.Columns.Add("S_CELL1_MRCount", typeof(string));
            dt.Columns.Add("S_CELL1_WeakMRCount", typeof(string));
            dt.Columns.Add("S_CELL2", typeof(string));
            dt.Columns.Add("S_CELL2_MRCount", typeof(string));
            dt.Columns.Add("S_CELL2_WeakMRCount", typeof(string));
            dt.Columns.Add("S_CELL3", typeof(string));
            dt.Columns.Add("S_CELL3_MRCount", typeof(string));
            dt.Columns.Add("S_CELL3_WeakMRCount", typeof(string));
            dt.Columns.Add("HasInDoor", typeof(string));
            string sqlText = @"With CET_10 AS ( 
	Select ROW_NUMBER()Over(order by P_Day desc)  RowNumber ,P_Day,City,BuildingId,BuildingName,Longitude,Latitude,FloorCount
,Floor,GridCount,WeakGridCount,WeakGridPercent,NoGridCount,NoGridPercent,MRCount,WeakMrCount,AvgMRRsrp,MRCoverPercent,S_CELL1,
S_CELL1_MRCount,S_CELL1_WeakMRCount,S_CELL2,S_CELL2_MRCount,S_CELL2_WeakMRCount,S_CELL3,S_CELL3_MRCount,
S_CELL3_WeakMRCount,HasInDoor from " + tableName + sqlCondition + @"
            ) Select * From CET_10 Where RowNumber  
Between (case " + PageIndex + @" when 1 then " + PageSize * (PageIndex - 1) + @" else " + (PageSize * (PageIndex - 1) + 1) + @" 
end) And " + PageSize * PageIndex + @"";
            string sqlCount = "select count(0) from " + tableName + sqlCondition;
            using (OracleConnection conn = new OracleConnection(connString))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlText;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 60 * 12 * 60;
                    using (OracleDataReader odr = cmd.ExecuteReader())
                    {
                        while (odr.Read())
                        {
                            dt.Rows.Add(
                                odr["P_Day"].ToString(),
                                odr["City"].ToString(),
                                odr["BuildingId"].ToString(),
                                odr["BuildingName"].ToString(),
                                odr["Longitude"].ToString(),
                                odr["Latitude"].ToString(),
                                odr["FloorCount"].ToString(),
                                odr["Floor"].ToString(),
                                odr["GridCount"].ToString(),
                                odr["WeakGridCount"].ToString(),
                                odr["WeakGridPercent"].ToString(),
                                odr["NoGridCount"].ToString(),
                                odr["NoGridPercent"].ToString(),
                                odr["MRCount"].ToString(),
                                odr["WeakMrCount"].ToString(),
                                odr["AvgMRRsrp"].ToString(),
                                odr["MRCoverPercent"].ToString(),
                                odr["S_CELL1"].ToString(),
                                odr["S_CELL1_MRCount"].ToString(),
                                odr["S_CELL1_WeakMRCount"].ToString(),
                                odr["S_CELL2"].ToString(),
                                odr["S_CELL2_MRCount"].ToString(),
                                odr["S_CELL2_WeakMRCount"].ToString(),
                                odr["S_CELL3"].ToString(),
                                odr["S_CELL3_MRCount"].ToString(),
                                odr["S_CELL3_WeakMRCount"].ToString(),
                                odr["HasInDoor"].ToString()
                                );
                        }
                    }
                }
                conn.Close();
                using (OracleCommand command = conn.CreateCommand())
                {
                    conn.Open();
                    command.CommandType = CommandType.Text;
                    command.CommandTimeout = 12 * 60 * 60;
                    command.CommandText = sqlCount;
                    TotalCount = Convert.ToInt32(command.ExecuteScalar());
                }
                conn.Close();
            }
            return dt;
        }

        /// <summary>
        /// 查询结果导出
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="sqlCondition"></param>
        /// <returns></returns>
        public List<FlooorStatistics> GetFlooorStatisticsByWhere(string tableName, string sqlCondition)
        {
            List<FlooorStatistics> list = new List<FlooorStatistics>();
            using (OracleConnection conn = new OracleConnection(connString))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from " + tableName + sqlCondition;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 60 * 12 * 60;
                    using (OracleDataReader odr = cmd.ExecuteReader())
                    {
                        while (odr.Read())
                        {
                            FlooorStatistics ss = new FlooorStatistics
                            {
                                P_Day = Convert.ToDateTime(odr["P_Day"]),
                                City = odr["City"].ToString(),
                                BuildingId = odr["BuildingId"].ToString(),
                                BuildingName = odr["BuildingName"].ToString(),
                                Longitude = Convert.ToDouble(odr["Longitude"]),
                                Latitude = Convert.ToDouble(odr["Latitude"]),
                                FloorCount = Convert.ToInt32(odr["FloorCount"]),
                                Floor = Convert.ToInt32(odr["Floor"]),
                                GridCount = Convert.ToInt32(odr["GridCount"]),
                                WeakGridCount = Convert.ToInt32(odr["WeakGridCount"]),
                                WeakGridPercent = Convert.ToDouble(odr["WeakGridPercent"]),
                                NoGridCount = Convert.ToInt32(odr["NoGridCount"]),
                                NoGridPercent = Convert.ToDouble(odr["NoGridPercent"]),
                                MRCount = Convert.ToInt32(odr["MRCount"]),
                                WeakMrCount = Convert.ToInt32(odr["WeakMrCount"]),
                                AvgMRRsrp = Convert.ToDouble(odr["AvgMRRsrp"]),
                                MRCoverPercent = Convert.ToDouble(odr["MRCoverPercent"]),
                                S_CELL1 = odr["S_CELL1"].ToString(),
                                S_CELL1_MRCount = Convert.ToInt32(odr["S_CELL1_MRCount"]),
                                S_CELL1_WeakMRCount = Convert.ToInt32(odr["S_CELL1_WeakMRCount"]),
                                S_CELL2 = odr["S_CELL2"].ToString(),
                                S_CELL2_MRCount = Convert.ToInt32(odr["S_CELL2_MRCount"]),
                                S_CELL2_WeakMRCount = Convert.ToInt32(odr["S_CELL2_WeakMRCount"]),
                                S_CELL3 = odr["S_CELL3"].ToString(),
                                S_CELL3_MRCount = Convert.ToInt32(odr["S_CELL3_MRCount"]),
                                S_CELL3_WeakMRCount = Convert.ToInt32(odr["S_CELL3_WeakMRCount"]),
                                HasInDoor = Convert.ToInt32(odr["HasInDoor"])
                            };
                            list.Add(ss);
                        }
                    }
                }
            }
            return list;
        }
    }
}
